* Sets local data location.
* CHANGE THIS TO MATCH CURRENT COMPUTERn

*************Constructing composition of small bank deposit changes*****************************
***Output data   "$LocalData\Paper\Figures\SmallBankDepChangeDecomp.csv"
***Input data    SOD, CPI, Merger
use "$LocalData\SOD_Data_collapsed.dta", clear
sort cert year
collapse (sum) depsumbr (max) asset, by(cert year)

merge m:1 year using "$LocalData\CPI.dta"

replace asset = asset/cpaltt01usa661s
gen small = asset<1000000

*Categorizing banks as big or small and creating a variable (a_small) to merge with merger data to know acquirer size
*Such banks last appearing as small in year t will be mergers in year t+1
preserve
	keep cert small year
	sor cert year
	collapse (max) small, by(cert year)
	rename small a_small
	replace year = year+1
	sort cert year
	save "$LocalData\SmallAcquired.dta", replace
*	replace year = year - 1
	*To count acquiring large banks that were small in year Y not as having grown out organically

restore

*Categorizing banks as big or small and creating a variable (c_small) to merge with merger data to know acquired size
*Such banks last appearing in year t will be mergers in year t+1
preserve
	keep cert small year
	rename small c_small
	rename cert c_cert
	replace year = year+1
	sort c_cert year
	save "$LocalData\SmallClosed.dta", replace
restore


insheet using "$SourceData\MERG1712.csv", clear
keep if closed==1

drop if year<2000 | year>2017
drop if cert==0
drop if cert==c_cert
*align merger calendar with SOD calendar (June 30, YYYY)
replace year = year + 1 if qtr>2

preserve
*List of all acquirers in a given year. Use this to flag small banks in t-1 that grow to a large bank in t through acquisition
	gen acquirer=1
	keep cert year acquirer
	sort cert year
	collapse (max) acquirer, by (cert year)
	save "$LocalData\SmallAcquired2.dta", replace
restore
	
sort c_cert year
merge 1:1 c_cert year using "$LocalData\SmallClosed.dta"
keep if _merge==3

drop _merge
sort cert year
merge m:1 cert year using "$LocalData\SmallAcquired.dta"
keep if _merge==3

*Pulling back mergers to year prior
replace year = year-1
drop cert
rename c_cert cert
keep if a_small == 0 & c_small==1
keep cert year a_small c_small
sort cert year
*Data set of all small banks acquired by large banks
save "$LocalData\MergerChangeStatus.dta", replace


use "$LocalData\SOD_Data_collapsed.dta", clear
sort cert year
collapse (sum) depsumbr (max) asset, by (cert year)
sort year
merge m:1 year using "$LocalData\CPI.dta"

replace asset = asset/cpaltt01usa661s
*replace depsumbr = depsumbr/cpaltt01usa661s
gen small = asset<1000000
sort cert year
keep cert year small depsumbr
merge 1:1 cert year using "$LocalData\MergerChangeStatus.dta" 
drop _merge

sort cert year
xtset cert year
*Categorizing growing out of large
gen grow_out = 1 if small==0 & L.small==1

****************************
*Categorizing growing into small 
gen grow_in = 1 if small==1 & L.small==0

*Flagging deposits associated with banks that grew in or out of small status
gen depdom_in = depsumbr if grow_in ==1
gen depdom_out = depsumbr if grow_out ==1

*Categorizing deposits merged out through acquisition through acquisition of a large bank
*Bank acquired by another small bank will be caterogized only if the resulting bank in large (merge_out2)
gen merge_out = depsumbr if a_small ==0 & c_small==1

gen depsmall = depsumbr if small==1
gen deplarge = depsumbr if small==0

*Categorizing deposits merged out of small by combination of small
merge 1:1 cert year using "$LocalData\SmallAcquired2.dta"

*Small banks that grew out through merging
gen merge_out2 = depsumbr if acquirer ==1  & grow_out==1

*Recatergorizing growing out of small if there was an acquisition
replace grow_out = 0 if merge_out2!=.


sort year
collapse (sum) depdom_in depdom_out depsmall deplarge merge*, by (year)


tsset year
gen merge_small_large = L.merge_out
gen net_out = depdom_out - depdom_in - merge_out2

gen smallshare = depsmall/(depsmall+deplarge)
gen mergeoutshare = (merge_small_large + merge_out2)*L.smallshare/(depsmall+deplarge)
gen merged = merge_small_large + merge_out2

gen growoutshare = (net_out)*L.smallshare/(depsmall+deplarge)
gen smallgr   = (depsmall-L.depsmall+merge_small_large + merge_out2+net_out)*(L.smallshare)/(depsmall+deplarge)
gen largegr   = -(deplarge-L.deplarge-merge_small_large - merge_out2 - net_out)*(L.smallshare)/(depsmall+deplarge)
gen ddepsmall = d.depsmall + merge_small_large + merge_out2+net_out
gen ddepsmallnet = d.depsmall

gen dsmalldep_organic = ddepsmall/1000000
gen dsmalldep_net = ddepsmallnet/1000000
gen mergedout_small = merged/1000000
gen growthout_small = net_out/1000000

merge 1:1 year using "$LocalData\CPI.dta"
replace dsmalldep_net = dsmalldep_net/cpaltt01usa661s
replace mergedout_small = -mergedout_small/cpaltt01usa661s
replace growthout_small = -growthout_small/cpaltt01usa661s
replace dsmalldep_organic = dsmalldep_organic/cpaltt01usa661s
keep year dsmalldep_net mergedout_small growthout_small dsmalldep_organic
keep if year>1999 & year<2018

*Data for Figure 7
export delimited "$LocalData\Results\SmallBankDepChangeDecomp.csv", comma 




********************************************************************************


*Data for Figure 3
*Source FDIC RIS using RIS dictionary definitions of loan categories in Call Reports
insheet using "$SourceData\LoanDataFig3.csv", clear
sort year
merge year using "$LocalData\CPI.dta"
gen asset2 = asset/cpaltt01usa661s
gen small = asset2<1000000

keep if small==1
replace lnreag = . if lnreag==.
gen lnci_notsmall = lnci - lnci4
gen lnagtot = lnag + lnreag
collapse (sum) lnrenres lnreres lnci4 lnci_notsmall lnagtot lnls asset lnag lnag4, by (year)
gen lnlsoth = lnls - lnrenres - lnreres - lnci4 - lnci_notsmall - lnagtot
gen assetoth = asset - lnls

gen lnrenres_pct 		= lnrenres/asset
gen lnreres_pct 		= lnreres/asset
gen lnagtot_pct 		= lnagtot/asset
gen lnci_notsmall_pct 	= lnci_notsmall/asset
gen lnci4_pct 			= lnci4/asset
gen lnlsoth_pct			= lnlsoth/asset
gen assetoth_pct		= assetoth/asset
gen lnag_pct			= lnag/asset
gen lnagsm_pct			= lnag4/asset

gen Small_CIAg = (lnagsm_pct + lnci4_pct)*100
gen All_CIAg   = (lnci/asset + lnag_pct)*100

gen zero = 0
gen g1 = lnci4_pct
gen g2 = g1 + lnci_notsmall_pct
gen g3 = g2 + lnagtot_pct
gen g4 = g3 + lnrenres_pct
gen g5 = g4 + lnreres_pct
gen g6 = g5 + lnlsoth_pct
gen g7 = g6 + assetoth_pct


export delimited "$LocalData\Results\AssetComp.csv",  replace

